﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DataAccess.IService;
using System.Data;
using Utility;
using System.Collections;
using System.Data.SqlClient;

namespace DataAccess.Service
{
    public class AcademyService : IAcademyService
    {
        public DataTable getAcademyTable()
        {
            string CommandText = "select * from Academy";
            string[] colNames = new string[]
            {
                "acaId",
                "acaName"
            };
            return SQLHelper.generateDataTable(CommandText, colNames);
        }


        public Hashtable getAcademyHashtable()
        {
            Hashtable ht = new Hashtable();


            string commandText = string.Format("select * from Academy");
            SqlDataReader sdReader = SQLHelper.ExecuteReader(commandText);
            while (sdReader.Read())
            {
                ht.Add(sdReader["acaName"].ToString(), sdReader["acaId"].ToString());
            }

            return ht;
        }

        //用于发布缴费项时，获取所有专业
        public DataSet selectMajor()
        {
            string commandText = "select * from Major";
            return SQLHelper.ExecuteDataSet(commandText);
        }

        //得到主修专业所有学生的名单
        public List<string> getTargetByFirMaj(String target)
        {
           
            List<string> studentId=new List<string>();
            string commandText = "select stuId from Student where firstMajor=@firstMajor";
             SqlParameter[] paras=new SqlParameter[]{
                 new SqlParameter("firstMajor",target)
             };
            SqlDataReader objReader = SQLHelper.ExecuteReader(commandText, paras);
         
                for (; objReader.Read(); )
                {
                    studentId.Add(Convert.ToString(objReader["stuId"]));
                }

            return studentId;
        }

        public List<string> getTargetBySecMaj(String target)
        {
            List<string> studentId = new List<string>();
            string commandText = "select stuId from Student where secondMajor=@secondMajor";
            SqlParameter[] paras=new SqlParameter[]{
                new SqlParameter("secondMajor", target)
            };
            SqlDataReader objReader =SQLHelper.ExecuteReader(commandText,paras);
            for (; objReader.Read(); )
            {
                    studentId.Add(Convert.ToString(objReader["studentId"]));
                }

            
           
            return studentId;
        }
     

        public DataSet getAccadamy()
        {
            
            String commandText = "select * from Academy";
             return SQLHelper.ExecuteDataSet(commandText);   
        
        }
        public DataSet getMajor(String acadamyId)
        {
            String commandText = "select * from Major where majId like '%"+acadamyId+"%'";
            return SQLHelper.ExecuteDataSet(commandText);
        }
        public DataTable getMajorTable()
        {
            string CommandText = "select * from Major";
            string[] colNames = new string[]
            {
                "majId",
                "majName",
                "acaId"
            };
            return SQLHelper.generateDataTable(CommandText, colNames);
        }


        public Hashtable getMajorHashtable()
        {
            Hashtable ht = new Hashtable();


            string commandText = string.Format("select * from Major");
            SqlDataReader sdReader = SQLHelper.ExecuteReader(commandText);
            while (sdReader.Read())
            {
                ht.Add(sdReader["majName"].ToString(), sdReader["majId"].ToString());
            }

            return ht;
        }



        public DataSet getClassSetByMajorIdAndGrade(String majId, int grade)
        {
            String commandText = "select * from Class where majId = @majId and grade = @grade";
            SqlParameter[] paras = new SqlParameter[]{
                 new SqlParameter("@majId",majId),
                 new SqlParameter("@grade",grade)
            };
            return SQLHelper.ExecuteDataSet(commandText,CommandType.Text,paras);
        }
    }
}
